package com.apes.fn.ssm.repository;

import com.apes.fn.ssm.model.StoreMemberModels;
import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.account.account.model.Stock;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface StoreMemberModelsRepository extends CustomRepository<StoreMemberModels, String> {
    @Query(value = "" +
            "SELECT LOCATION_ID, STORE_CODE, CAR_LICENSE, JEKUN_MODEL_CODE, " +
            "       DECODE(VIP_LIVENESS_ID, 0, '新会员', 1, '活跃会员', 2, '非活跃会员', 3, '将流失会员', " +
            "               4, '已流失会员', 5, '非常活跃会员', 6, '无消费会员', '') VIP_LIVENESS_ID, " +
            "       MOBILE " +
            "  FROM STOCK_STORE_MEMBER_MODELS " +
            " WHERE LOCATION_ID = ?1 " +
            "   AND LOCATION_ID NOT IN (SELECT LOCATION_ID FROM STOCK_STORE_MEMBER_MODELS_I) " +
            "   AND VIP_LIVENESS_ID IN ('0', '1', '5')" +
            " UNION ALL " +
            "SELECT LOCATION_ID, STORE_CODE, CAR_LICENSE, JEKUN_MODEL_CODE, " +
            "       DECODE(VIP_LIVENESS_ID, 0, '新会员', 1, '活跃会员', 2, '非活跃会员', 3, '将流失会员', " +
            "               4, '已流失会员', 5, '非常活跃会员', 6, '无消费会员', '') VIP_LIVENESS_ID, " +
            "       '' MOBILE " +
            "  FROM STOCK_STORE_MEMBER_MODELS_I " +
            " WHERE LOCATION_ID = ?1 " +
            "   AND VIP_LIVENESS_ID IN ('0', '1', '5')",
            countQuery ="" +
                    "SELECT COUNT(*) " +
                    "  FROM (SELECT LOCATION_ID, STORE_CODE CAR_LICENSE, JEKUN_MODEL_CODE, " +
                    "                DECODE(VIP_LIVENESS_ID, 0, '新会员', 1, '活跃会员', 2, '非活跃会员', 3, " +
                    "                        '将流失会员', 4, '已流失会员', 5, '非常活跃会员', 6, '无消费会员', '') VIP_LIVENESS_ID, " +
                    "                MOBILE " +
                    "           FROM STOCK_STORE_MEMBER_MODELS " +
                    "          WHERE LOCATION_ID = ?1 AND VIP_LIVENESS_ID IN ('0', '1', '5') " +
                    "            AND LOCATION_ID NOT IN (SELECT LOCATION_ID FROM STOCK_STORE_MEMBER_MODELS_I) " +
                    "         UNION ALL " +
                    "         SELECT LOCATION_ID, STORE_CODE CAR_LICENSE, JEKUN_MODEL_CODE, " +
                    "                DECODE(VIP_LIVENESS_ID, 0, '新会员', 1, '活跃会员', 2, '非活跃会员', 3, " +
                    "                        '将流失会员', 4, '已流失会员', 5, '非常活跃会员', 6, '无消费会员', '') VIP_LIVENESS_ID, " +
                    "                '' MOBILE " +
                    "           FROM STOCK_STORE_MEMBER_MODELS_I " +
                    "          WHERE LOCATION_ID = ?1 AND VIP_LIVENESS_ID IN ('0', '1', '5') " +
                    ")",
            nativeQuery = true)
    Page<Map> stepVipModelDownload(String locationId, Pageable pageable);

    @Query(value = "" +
            "SELECT LOCATION_ID, STORE_CODE, CAR_LICENSE, JEKUN_MODEL_CODE, " +
            "       DECODE(VIP_LIVENESS_ID, 0, '新会员', 1, '活跃会员', 2, '非活跃会员', 3, '将流失会员', " +
            "               4, '已流失会员', 5, '非常活跃会员', 6, '无消费会员', '') VIP_LIVENESS_ID, " +
            "       MOBILE " +
            "  FROM STOCK_STORE_MEMBER_MODELS " +
            " WHERE LOCATION_ID = ?1 " +
            "   AND STORE_CODE IN (SELECT STORE_ID FROM V_STOCK_CALCU_STORE WHERE F_LOCATION_ID = ?2) " +
            "   AND LOCATION_ID NOT IN (SELECT LOCATION_ID FROM STOCK_STORE_MEMBER_MODELS_I) " +
            "   AND VIP_LIVENESS_ID IN ('0', '1', '5') " +
            " UNION ALL " +
            "SELECT LOCATION_ID, STORE_CODE, CAR_LICENSE, JEKUN_MODEL_CODE, " +
            "       DECODE(VIP_LIVENESS_ID, 0, '新会员', 1, '活跃会员', 2, '非活跃会员', 3, '将流失会员', " +
            "               4, '已流失会员', 5, '非常活跃会员', 6, '无消费会员', '') VIP_LIVENESS_ID, " +
            "       '' MOBILE " +
            "  FROM STOCK_STORE_MEMBER_MODELS_I " +
            " WHERE LOCATION_ID = ?1 " +
            "   AND STORE_CODE IN (SELECT STORE_ID FROM V_STOCK_CALCU_STORE WHERE F_LOCATION_ID = ?2) " +
            "   AND VIP_LIVENESS_ID IN ('0', '1', '5') ",
            countQuery = "" +
                    "SELECT COUNT(*) " +
                    "  FROM (SELECT LOCATION_ID, STORE_CODE CAR_LICENSE, JEKUN_MODEL_CODE, " +
                    "                DECODE(VIP_LIVENESS_ID, 0, '新会员', 1, '活跃会员', 2, '非活跃会员', 3, " +
                    "                        '将流失会员', 4, '已流失会员', 5, '非常活跃会员', 6, '无消费会员', '') VIP_LIVENESS_ID, " +
                    "                MOBILE " +
                    "           FROM STOCK_STORE_MEMBER_MODELS " +
                    "          WHERE LOCATION_ID = ?1 " +
                    "            AND LOCATION_ID NOT IN (SELECT LOCATION_ID FROM STOCK_STORE_MEMBER_MODELS_I) " +
                    "            AND STORE_CODE IN (SELECT STORE_ID FROM V_STOCK_CALCU_STORE WHERE F_LOCATION_ID = ?2) " +
                    "         UNION ALL " +
                    "         SELECT LOCATION_ID, STORE_CODE CAR_LICENSE, JEKUN_MODEL_CODE, " +
                    "                DECODE(VIP_LIVENESS_ID, 0, '新会员', 1, '活跃会员', 2, '非活跃会员', 3, " +
                    "                        '将流失会员', 4, '已流失会员', 5, '非常活跃会员', 6, '无消费会员', '') VIP_LIVENESS_ID, " +
                    "                '' MOBILE " +
                    "           FROM STOCK_STORE_MEMBER_MODELS_I " +
                    "          WHERE LOCATION_ID = ?1 " +
                    "            AND STORE_CODE IN (SELECT STORE_ID FROM V_STOCK_CALCU_STORE WHERE F_LOCATION_ID = ?2) " +
                    ") " +
                    "            AND VIP_LIVENESS_ID IN ('0', '1', '5') ",
            nativeQuery = true)
    Page<Map> stepVipModelDownloadF(String locationId, String copyLocationId, Pageable pageable);

    @Query(value = "" +
            "SELECT LOCATION_ID, " +
            "       (SELECT NAME FROM MAS_LOCATION WHERE ID = A.LOCATION_ID) LOCATION_NAME, " +
            "       FITTING_MESSAGE_ID, BRAND_PRIORITY, " +
            "       (SELECT DESCRIPTION FROM MAS_ENUMERATION J WHERE J.ID = A.BRAND_PRIORITY) BRAND_PRIORITY_NAME, " +
            "       PRODUCT_ID, PZCXID " +
            "  FROM MAS_STOCK_CACLU_RESULT A " +
            " WHERE A.LOCATION_ID = ?1 " +
            "   AND FITTING_MESSAGE_ID = ?2 ",
            countQuery = "" +
                    "SELECT COUNT(1) " +
                    "  FROM MAS_STOCK_CACLU_RESULT A " +
                    " WHERE A.LOCATION_ID = ?1 " +
                    "   AND FITTING_MESSAGE_ID = ?2 ",
            nativeQuery = true)
    Page<Map> stepTwoModelPro(String locationId, String fittingMessageOd, Pageable pageable);

    @Query(value = "" +
            "SELECT LOCATION_ID, " +
            "       (SELECT NAME FROM MAS_LOCATION WHERE ID = A.LOCATION_ID) LOCATION_NAME, " +
            "       FITTING_MESSAGE_ID, " +
            "       (SELECT NAME FROM MAS_FITTING_MESSAGE WHERE ID = A.FITTING_MESSAGE_ID) FITTING_MESSAGE_NAME, " +
            "       BRAND_PRIORITY_ID, " +
            "       (SELECT DESCRIPTION FROM MAS_ENUMERATION J WHERE J.ID = A.BRAND_PRIORITY_ID) BRAND_PRIORITY_NAME, " +
            "       BRAND_PRIORITY_SERIAL_NUMBER, COUNT_MODEL, " +
            "       MODEL_SERIAL_NUMBER, (SELECT NAME FROM MAS_PRODUCT J WHERE J.ID = A.PRODUCT_ID) PRODUCT_NAME " +
            "  FROM STOCK_CALCU_RESULT A " +
            " WHERE MODEL_SERIAL_NUMBER = 1 AND LOCATION_ID = ?1 " +
            "   AND FITTING_MESSAGE_ID = ?2 ",
    countQuery = "" +
            "SELECT COUNT(1) " +
            "  FROM STOCK_CALCU_RESULT A " +
            " WHERE MODEL_SERIAL_NUMBER = 1 AND LOCATION_ID = ?1 " +
            "   AND FITTING_MESSAGE_ID = ?2 ",
    nativeQuery = true)
    Page<Map> stepTwoQualifying(String locationId, String fittingMessageOd, Pageable pageable);

    @Query(value = "" +
            "SELECT LOCATION_ID, PRODUCT_ID, (SELECT NAME FROM MAS_PRODUCT J WHERE J.ID = A.PRODUCT_ID) PRODUCT_NAME, " +
            "       (SELECT NAME FROM MAS_LOCATION WHERE ID = A.LOCATION_ID) LOCATION_NAME, " +
            "       FITTING_MESSAGE_ID, " +
            "       (SELECT NAME FROM MAS_FITTING_MESSAGE WHERE ID = A.FITTING_MESSAGE_ID) FITTING_MESSAGE_NAME, " +
            "       NVL(MATCH_SALES, 0) MATCH_SALES, NVL(ACTUAL_SALES, 0) ACTUAL_SALES " +
            "  FROM STOCK_CALCU_RESULT A " +
            " WHERE MODEL_SERIAL_NUMBER = 1 AND BRAND_PRIORITY_SERIAL_NUMBER = 1 AND LOCATION_ID = ?1 " +
            "   AND FITTING_MESSAGE_ID = ?2 ",
            countQuery = "" +
                    "SELECT COUNT(1) " +
                    "  FROM STOCK_CALCU_RESULT A " +
                    " WHERE MODEL_SERIAL_NUMBER = 1 AND BRAND_PRIORITY_SERIAL_NUMBER = 1 AND LOCATION_ID = ?1 " +
                    "   AND FITTING_MESSAGE_ID = ?2 ",
            nativeQuery = true)
    Page<Map> stepTwoSales(String locationId, String fittingMessageOd, Pageable pageable);


    @Query(value = "" +
            "SELECT LOCATION_ID, (SELECT NAME FROM MAS_PRODUCT J WHERE J.ID = A.PRODUCT_ID) PRODUCT_NAME, " +
            "       (SELECT NAME FROM MAS_LOCATION WHERE ID = A.LOCATION_ID) LOCATION_NAME, " +
            "       FITTING_MESSAGE_ID, " +
            "       (SELECT NAME FROM MAS_FITTING_MESSAGE WHERE ID = A.FITTING_MESSAGE_ID) FITTING_MESSAGE_NAME, " +
            "       NVL(ACTIVE_MEMBERS_NUMBER, 0) ACTIVE_MEMBERS_NUMBER " +
            "  FROM STOCK_CALCU_RESULT A " +
            " WHERE MODEL_SERIAL_NUMBER = 1 AND BRAND_PRIORITY_SERIAL_NUMBER = 1 AND LOCATION_ID = ?1 " +
            "   AND FITTING_MESSAGE_ID = ?2 ",
            countQuery = "" +
                    "SELECT COUNT(1) " +
                    "  FROM STOCK_CALCU_RESULT A " +
                    " WHERE MODEL_SERIAL_NUMBER = 1 AND BRAND_PRIORITY_SERIAL_NUMBER = 1 AND LOCATION_ID = ?1 " +
                    "   AND FITTING_MESSAGE_ID = ?2 ",
            nativeQuery = true)
    Page<Map> stepTwoMemberCoverage(String locationId, String fittingMessageOd, Pageable pageable);

    @Query(value = "" +
            "SELECT LOCATION_ID, " +
            "       (SELECT NAME FROM MAS_LOCATION WHERE ID = A.LOCATION_ID) LOCATION_NAME, " +
            "       FITTING_MESSAGE_ID, " +
            "       (SELECT NAME FROM MAS_FITTING_MESSAGE WHERE ID = A.FITTING_MESSAGE_ID) FITTING_MESSAGE_NAME, " +
            "       PRODUCT_ID, (SELECT NAME FROM MAS_PRODUCT WHERE ID = A.PRODUCT_ID) PRODUCT_NAME " +
            "  FROM STOCK_CALCU_RESULT A " +
            " WHERE STOCK_MARK = 1 AND BRAND_PRIORITY_SERIAL_NUMBER = 1 AND MODEL_SERIAL_NUMBER = 1 AND LOCATION_ID = ?1 " +
            "   AND FITTING_MESSAGE_ID = ?2 ",
            countQuery = "" +
                    "SELECT COUNT(1) " +
                    "  FROM STOCK_CALCU_RESULT A " +
                    " WHERE STOCK_MARK = 1 AND BRAND_PRIORITY_SERIAL_NUMBER = 1 AND MODEL_SERIAL_NUMBER = 1 AND LOCATION_ID = ?1 " +
                    "   AND FITTING_MESSAGE_ID = ?2 ",
            nativeQuery = true)
    Page<Map> stepTwoStockPro(String locationId, String fittingMessageOd, Pageable pageable);

    @Query(value = "" +
            "SELECT LOCATION_ID, PRODUCT_ID, (SELECT NAME FROM MAS_PRODUCT J WHERE J.ID = A.PRODUCT_ID) PRODUCT_NAME, " +
            "       (SELECT NAME FROM MAS_LOCATION WHERE ID = A.LOCATION_ID) LOCATION_NAME, " +
            "       FITTING_MESSAGE_ID, " +
            "       (SELECT NAME FROM MAS_FITTING_MESSAGE WHERE ID = A.FITTING_MESSAGE_ID) FITTING_MESSAGE_NAME, " +
            "       SAFETY_STOCK_QTY, THE_MAX_QTY, THE_MIN_QTY " +
            "  FROM STOCK_CALCU_RESULT A " +
            " WHERE MODEL_SERIAL_NUMBER = 1 AND BRAND_PRIORITY_SERIAL_NUMBER = 1 AND STOCK_MARK = 1 AND LOCATION_ID = ?1 " +
            "   AND FITTING_MESSAGE_ID = ?2 ",
            countQuery = "" +
                    "SELECT COUNT(1) " +
                    "  FROM STOCK_CALCU_RESULT A " +
                    " WHERE MODEL_SERIAL_NUMBER = 1 AND BRAND_PRIORITY_SERIAL_NUMBER = 1 AND STOCK_MARK = 1 AND LOCATION_ID = ?1 " +
                    "   AND FITTING_MESSAGE_ID = ?2 ",
            nativeQuery = true)
    Page<Map> stepTwoStockQty(String locationId, String fittingMessageOd, Pageable pageable);

    @Query(value = "" +
            "SELECT ROWNUM FROM STOCK_CALCU_RESULT WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_CALCU_VIP WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_CXDZML_OEPJ WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_CXDZML_CENTER WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_CXDZML_PRO WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_CXDZML_CXPP WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_STOCK_CACLU_RESULT WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_CXPPGZ_CENTER WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_CXPPGZ_DIS_PZ WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_CXPPGZ_PRO WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_CXPPGZ_CXPP WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_STOCK_CALCU_PW WHERE ROWNUM = 1 " +
            "UNION ALL " +
            "SELECT ROWNUM FROM MAS_CALCU_PRO_SALES WHERE ROWNUM = 1 ", nativeQuery = true)
    List queryClearComplete();
}
